﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI.WebControls;
using Yipusoft.Account.Model;
using Yipusoft.Common.BLL;
using Yipusoft.Evaluate.BLL;
using System.Text;
using System.Data;
using Yipusoft.Evaluate.Model;

public partial class Evaluate_CheckReportD : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Yipusoft.Account.BLL.User.HasPermission(Context.User.Identity.Name, "100103"))
        {
            //Response.Write("<script>alert('没有权限！');history.back(0);</script>");
        }
        if (!IsPostBack)
        {
            BindYearList();
            CreateTable();
        }
    }
    private void BindYearList()
    {
        for (int i = 2008; i <= 2050; i++)
        {
            ListItem li = new ListItem();
            li.Text = i.ToString() + "年";
            li.Value = i.ToString();
            YearList.Items.Add(li);
        }
        YearList.SelectedValue = DateTime.Now.Year.ToString();
    }

    private string GetWhere()
    {
        string DepartmentID = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;
        string ReturnStr = string.Empty;

        List<RoleInfo> list = Yipusoft.Account.BLL.User.GetRoleList(Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).UserID);

        bool Flag = list.Where(o => o.RoleID.Equals("100110")).Count() > 0;
        if (Flag)
        {
            ReturnStr = "And SubString(DepartmentID,6,1) IN('1','2','3','4') And  SubString(DepartmentID,5,1) ='0'";
        }
        else
        {
            //总队人员
            if (DepartmentID.Substring(3, 2).Equals("00"))
            {
                ReturnStr = " And SubString(DepartmentID,6,1)='" + DepartmentID.Substring(5, 1) + "' And SubString(DepartmentID,5,1)='0'";
            }
            else
            {
                //如果属于支队司政后防
                if (DepartmentID.Substring(4, 1).Equals("0"))
                {
                    ReturnStr = " And Left(DepartmentID,6)= '" + DepartmentID.Substring(0, 6) + "'";
                }

            }
        }

        return ReturnStr;

    }

    private int GetGroupByLen()
    {
        List<RoleInfo> list = Yipusoft.Account.BLL.User.GetRoleList(Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).UserID);

        //是否是司政后防管理员
        bool Flag = list.Where(o => o.RoleID.Equals("100110")).Count() > 0;
        if (Flag)
        {
            return 4;
        }
        else
        {
            return 6;
        }
    }

    private string GetDepartmentID()
    {
        string DepartmentID = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;
        
        return " a.DepartmentID like ''" + DepartmentID.Substring(0, 6) + "%'' And ";

    }

    private string GetDepartmentID2()
    {
        string DepartmentID = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;

        return " a.DepartmentID like '" + DepartmentID.Substring(0, 6) + "%' And ";

    }

    private string GetDepartmentIDChar()
    {
        string DepartmentID = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;
        return " DepartmentID like '" + DepartmentID.Substring(0, 6) + "%' And ";

    }
    private void CreateTable()
    {
        string departmentId = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;
        DataSet ds = SplitPage.ExecuteSQL("SELECT ShortName,DepartmentID FROM Account_Departments WHERE DepartmentId like '" + departmentId.Substring(0, 4) + rbType.SelectedValue + "%' order By DepartmentID");
        StringBuilder sql = new StringBuilder();
        sql.AppendLine("DECLARE @SQL nvarchar(4000)");
        sql.AppendLine("SET @SQL = ''");
        sql.AppendLine("SELECT @SQL = @SQL + ',SUM(CASE WHEN b.DepartmentId2 = ''' + DepartmentId + ''' THEN b.ItemScore END) AS D' + DepartmentId + '' FROM Account_Departments WHERE DepartmentId like '" + departmentId.Substring(0, 4) + rbType.SelectedValue + "%' order By DepartmentID");
        sql.AppendLine("SET @SQL = 'SELECT a.CheckID, a.CheckName' + @SQL + ' FROM Checks a LEFT JOIN CheckItems b ON a.CheckID = b.CheckID WHERE " + GetDepartmentID() + " YEAR(a.CheckDate)=" + YearList.SelectedValue + " and flag=" + rbType.SelectedValue + " group BY a.CheckID, a.CheckName,a.TotalScore'");
        sql.AppendLine("EXEC(@SQL)");
        if (SplitPage.ExecuteSQL(sql.ToString()) == null)
            return;
        if (SplitPage.ExecuteSQL(sql.ToString()).Tables.Count < 1)
            return;

        DataView dv = SplitPage.ExecuteSQL(sql.ToString()).Tables[0].DefaultView;

        Table tb = new Table();
        tb.Caption = "<span>" + YearList.SelectedItem.Text + "考核验收得分情况统计表</span>";

        tb.CellPadding = 4;
        tb.CellSpacing = 0;
        TableRow tr = new TableRow();
        TableCell tc = new TableCell();

        tc.Text = "";
        tc.Style.Add("white-space", "nowrap");

        tr.Cells.Add(tc);
        tc = new TableCell();
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Text = "考核验收名称";
        tc.Style.Add("white-space", "nowrap");
        tr.Cells.Add(tc);

        int col = ds.Tables[0].Rows.Count;

        for (int i = 0; i < col; i++)
        {
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Right;
            tc.Style.Add("white-space", "nowrap");
            tc.Text = ds.Tables[0].Rows[i][0].ToString();
            tr.Cells.Add(tc);
            tb.Rows.Add(tr);
        }
        int row = 1;
        foreach (DataRowView drv in dv)
        {
            CheckInfo info = Check.GetCheck(Convert.ToInt32(drv[0]));
            decimal TotalScore = info.TotalScore;
            tr = new TableRow();

            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Center;
            tc.Text = row.ToString();
            tc.Style.Add("white-space", "nowrap");
            tr.Cells.Add(tc);
            tc = new TableCell();
            tc.BackColor = System.Drawing.Color.White;
            tc.Text = Check.GetCheck((int)drv[0]).CheckName;
            tr.Cells.Add(tc);
            for (int i = 2; i <= col + 1; i++)
            {
                tc = new TableCell();
                tc.BackColor = System.Drawing.Color.White;
                tc.HorizontalAlign = HorizontalAlign.Center;
                string score = "";
                if (drv[i] != DBNull.Value)
                    score = (TotalScore - (decimal)drv[i]).ToString("0");
                else
                    score = TotalScore.ToString("0");
                tc.Text = "<a href=CheckViewD.aspx?CheckId=" + drv[0].ToString() + "&DepartmentId=" + dv.Table.Columns[i].ColumnName.Replace("D", "") + ">" + score + "</a>";
                tr.Cells.Add(tc);
            }
            tb.Rows.Add(tr);
            row++;
        }

        sql = new StringBuilder();
        sql.AppendLine("DECLARE @SQL nvarchar(max)");
        sql.AppendLine("DECLARE @TotalScore decimal(18,2)");
        sql.AppendLine("SELECT @TotalScore = SUM(TotalScore) FROM Checks WHERE flag=" + rbType.SelectedValue + " and DepartmentId like '" + departmentId.Substring(0, 6) + "%' and YEAR(CheckDate) = " + YearList.SelectedValue + "");
        sql.AppendLine("SET @SQL = ''");
        sql.AppendLine("SELECT @SQL = @SQL + ', ' + CAST(@TotalScore AS varchar(50)) + ' -case when SUM(CASE WHEN  b.DepartmentId2 = ''' + DepartmentId + ''' THEN b.ItemScore END) is null then 0 else SUM(CASE WHEN b.DepartmentId2 = ''' + DepartmentId + ''' THEN b.ItemScore END) end  AS D' + DepartmentId + '' FROM Account_Departments WHERE DepartmentId like '" + departmentId.Substring(0, 4) + rbType.SelectedValue + "%' Group By DepartmentID");
        sql.AppendLine("SET @SQL = 'SELECT 0' + @SQL + ' FROM Checks a LEFT JOIN CheckItems b ON a.CheckID = b.CheckID WHERE a.departmentId like ''" + departmentId.Substring(0, 6) + "%''and flag=" + rbType.SelectedValue + " and YEAR(a.CheckDate)=" + YearList.SelectedValue + "'");
        sql.AppendLine("EXEC(@SQL)");

        if (SplitPage.ExecuteSQL(sql.ToString()) == null)
            return;
        if (SplitPage.ExecuteSQL(sql.ToString()).Tables.Count < 1)
            return;
        dv = SplitPage.ExecuteSQL(sql.ToString()).Tables[0].DefaultView;
        tr = new TableRow();
        tr.CssClass = "grid_item";
        tc = new TableCell();
        tc.Text = "";

        tr.Cells.Add(tc);
        tc = new TableCell();
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Text = "合计";
        tr.Cells.Add(tc);
        for (int i = 1; i <= col; i++)
        {
            tc = new TableCell();
            tc.HorizontalAlign = HorizontalAlign.Center;

            if (dv[0][i] != DBNull.Value)
            {
                tc.Text = (decimal.Parse(dv[0][i].ToString())).ToString("0");
            }
            else
            {
                tc.Text = "0";
            }
            tr.Cells.Add(tc);
            tb.Rows.Add(tr);
        }
        PlaceHolder.Controls.Add(tb);

        BindChart();
    }
    private void BindChart()
    {
        string departmentId = Yipusoft.Account.BLL.User.GetUser(User.Identity.Name).DepartmentID;

        StringBuilder sql = new StringBuilder();
        sql.AppendLine("DECLARE @TotalScore decimal(18,2)");
        sql.AppendLine("Select @TotalScore = Sum(TotalScore) From Checks Where departmentId like '" + departmentId.Substring(0, 6) + "%' and flag=" + rbType.SelectedValue + " and Year(Checkdate) = " + YearList.SelectedValue + "");
        sql.Append("Select  DepartmentID,shortName, ");
        sql.Append("Cast(@TotalScore As Varchar(50))-case when (Select Sum(itemscore) From Checkitems Where Checkid In (Select Checkid From Checks Where " + GetDepartmentIDChar() + "  Year(Checkdate)=" + YearList.SelectedValue + ") And DepartmentID2 =Account_Departments.DepartmentID) is null then 0 else (Select Sum(itemscore) From Checkitems Where Checkid In (Select Checkid From Checks Where " + GetDepartmentIDChar() + "  Year(Checkdate)=" + YearList.SelectedValue + ") And DepartmentID2 = Account_Departments.DepartmentID) end As Score ");
        sql.Append("From Account_Departments Where DepartmentID like '" + departmentId.Substring(0, 4) + rbType.SelectedValue + "%'  Group by DepartmentID,shortName order by score desc");
        DataView dv = SplitPage.ExecuteSQL(sql.ToString()).Tables[0].DefaultView;
        UserChart.Titles["Title"].Text = YearList.SelectedItem.Text + "各单位考核验收得分情况统计表";
        UserChart.ChartAreas["Default"].AxisX.LabelsAutoFit = false;
        dv.Sort = " Score asc ";
        UserChart.Series["得分"].Points.DataBindXY(dv, "ShortName", dv, "Score");
        UserChart.Series[0].Type = Dundas.Charting.WebControl.SeriesChartType.Bar;
    }
    protected void YearList_SelectedIndexChanged(object sender, EventArgs e)
    {
        CreateTable();
    }
    protected void rbType_SelectedIndexChanged(object sender, EventArgs e)
    {
        CreateTable();
    }
}
